package cn.hf.mychat.common.poi;

import cn.hf.mychat.common.util.ReflectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.awt.Color;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.*;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author 白高建
 * @create 2020/1/20 11:56
 * 以对象的形式，通过注解 ExcelColumn 动态获取字段携带的多个信息
 * 无法用于动态表头
 * TODO 将全部错误信息更准确的（哪一行的那个字段以及错误信息）返回给前端。而不是一个地方出错就抛异常
 */
public class ExcelUtils<E> {
    private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
    private E row;
    private Class<E> cls;
    private ExcelResult<E> resultWrapper = new ExcelResult<>();
    private Map<String, ExcelColumn> fieldAnnotationMapper = new HashMap<>();
    private Field[] fields = null;


    public ExcelUtils(Class<E> cls) {
        this.cls = cls;
        try {
            this.fields = ReflectUtils.getClassFieldsAndSuperClassFields(cls);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (this.fields != null) {
            Field[] temps = this.fields;
            int length = temps.length;
            for(int i = 0; i < length; ++i) {
                Field field = temps[i];
                this.fieldAnnotationMapper.put(field.getName(), field.getAnnotation(ExcelColumn.class));
            }
        }
    }
    private E newRowData() {
        try {
            this.row = this.cls.newInstance();
        } catch (InstantiationException var2) {
            var2.printStackTrace();
        } catch (IllegalAccessException var3) {
            var3.printStackTrace();
        }

        return this.row;
    }

    public static void writeToFile(String filePath, String[] sheetName, List<? extends Object[]> title, List<? extends List<? extends Object[]>> data) throws FileNotFoundException, IOException {
        Workbook wb = getWorkBook(sheetName, title, data);
        FileOutputStream out = new FileOutputStream(filePath);
        wb.write(out);
        out.close();
    }

    public static Workbook getWorkBook(String[] sheetName, List<? extends Object[]> title, List<? extends List<? extends Object[]>> data) throws FileNotFoundException, IOException {
        Workbook wb = new SXSSFWorkbook();
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
        CellStyle titleStyle = wb.createCellStyle();
        CellStyle cellStyle = wb.createCellStyle();
        Font font = wb.createFont();
        font.setBold(true);
        titleStyle.setFont(font);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
        Object[] title_temp = null;
        Object[] rowData = null;
        List<? extends Object[]> sheetData = null;

        for(int sheetNumber = 0; sheetNumber < sheetName.length; ++sheetNumber) {
            sheet = wb.createSheet();
            sheet.setDefaultColumnWidth(18);
            wb.setSheetName(sheetNumber, sheetName[sheetNumber]);
            title_temp = (Object[])title.get(sheetNumber);
            row = sheet.createRow(0);

            int rowNumber;
            for(rowNumber = 0; rowNumber < title_temp.length; ++rowNumber) {
                cell = row.createCell(rowNumber);
                cell.setCellStyle(titleStyle);
                cell.setCellValue(title_temp[rowNumber].toString());
            }

            try {
                sheetData = (List)data.get(sheetNumber);
            } catch (Exception var16) {
                continue;
            }

            for(rowNumber = 0; rowNumber < sheetData.size(); ++rowNumber) {
                row = sheet.createRow(title_temp == null ? rowNumber : rowNumber + 1);
                rowData = (Object[])sheetData.get(rowNumber);

                for(int columnNumber = 0; columnNumber < rowData.length; ++columnNumber) {
                    cell = row.createCell(columnNumber);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(rowData[columnNumber] + "");
                }
            }
        }

        return wb;
    }

    public static <T> void writeToFile(List<T> list, ExcelDataFormatter edf, String filePath) throws Exception {
        Workbook wb = getWorkBook(list, edf);
        FileOutputStream out = new FileOutputStream(filePath);
        wb.write(out);
        out.close();
    }

    public static <T> Workbook getWorkBook(List<T> list, ExcelDataFormatter edf) throws Exception {
        Workbook wb = new SXSSFWorkbook();
        if (list != null && list.size() != 0) {
            Sheet sheet = wb.createSheet();
            Row row = sheet.createRow(0);
            row.setHeight((short)432);
            Cell cell = null;
            CreationHelper createHelper = wb.getCreationHelper();
            Field[] fields = ReflectUtils.getClassFieldsAndSuperClassFields(list.get(0).getClass());
            XSSFCellStyle titleStyle = (XSSFCellStyle)wb.createCellStyle();
            titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            titleStyle.setFillForegroundColor(new XSSFColor(new Color(159, 213, 183)));
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            Font font = wb.createFont();
            font.setColor(IndexedColors.BROWN.getIndex());
            font.setBold(true);
            titleStyle.setFont(font);
            List<Field> excelFieldList = new ArrayList();
            Field[] excelFields;
            int columnIndex;
            if (fields != null) {
                excelFields = fields;
                columnIndex = fields.length;

                for(int var13 = 0; var13 < columnIndex; ++var13) {
                    Field field = excelFields[var13];
                    ExcelColumn excelColumn = (ExcelColumn)field.getAnnotation(ExcelColumn.class);
                    if (null != excelColumn) {
                        excelFieldList.add(field);
                    }
                }
            }

            excelFields = new Field[0];
            excelFields = excelFieldList.toArray(excelFields);
            Arrays.sort(excelFields, new ExcelColumnCompare());
            columnIndex = 0;
            ExcelColumn excel = null;
            Field[] var28 = excelFields;
            int var30 = excelFields.length;

            for(int var16 = 0; var16 < var30; ++var16) {
                Field field = var28[var16];
                field.setAccessible(true);
                excel = (ExcelColumn)field.getAnnotation(ExcelColumn.class);
                if (excel != null && !excel.skip()) {
                    sheet.setColumnWidth(columnIndex, excel.width() * 256);
                    cell = row.createCell(columnIndex);
                    cell.setCellStyle(titleStyle);
                    cell.setCellValue(excel.name());
                    ++columnIndex;
                }
            }

            int rowIndex = 1;
            CellStyle cs = wb.createCellStyle();
            cs.setVerticalAlignment(VerticalAlignment.CENTER);

            for(Iterator var32 = list.iterator(); var32.hasNext(); ++rowIndex) {
                T t = (T) var32.next();
                row = sheet.createRow(rowIndex);
                row.setHeight((short)432);
                columnIndex = 0;
                Object o = null;
                Field[] var19 = excelFields;
                int var20 = excelFields.length;

                for(int var21 = 0; var21 < var20; ++var21) {
                    Field field = var19[var21];
                    field.setAccessible(true);
                    excel = (ExcelColumn)field.getAnnotation(ExcelColumn.class);
                    if (excel != null && !excel.skip()) {
                        cell = row.createCell(columnIndex);
                        o = field.get(t);
                        if (o == null) {
                            o = "";
                        }

                        if (o instanceof Date) {
                            cs.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
                            cell.setCellStyle(cs);
                            cell.setCellValue((Date)field.get(t));
                        } else if (!(o instanceof Double) && !(o instanceof Float)) {
                            Map map;
                            if (o instanceof Boolean) {
                                Boolean bool = (Boolean)field.get(t);
                                if (edf == null) {
                                    cell.setCellValue(bool);
                                } else {
                                    map = edf.get(field.getName());
                                    if (map == null) {
                                        cell.setCellValue(bool);
                                    } else {
                                        cell.setCellValue((String)map.get(bool.toString().toLowerCase()));
                                    }
                                }
                            } else if (o instanceof Integer) {
                                Integer intValue = (Integer)field.get(t);
                                if (edf == null) {
                                    cell.setCellValue((double)intValue);
                                } else {
                                    map = edf.get(field.getName());
                                    if (map == null) {
                                        cell.setCellValue((double)intValue);
                                    } else {
                                        cell.setCellValue((String)map.get(intValue.toString()));
                                    }
                                }
                            } else if (o instanceof BigDecimal) {
                                BigDecimal v = (BigDecimal)o;
                                DecimalFormat format = new DecimalFormat("0.00");
                                format.setRoundingMode(RoundingMode.HALF_UP);
                                String vformat = format.format(v);
                                if (edf == null) {
                                    cell.setCellValue(vformat);
                                } else {
                                    map = edf.get(field.getName());
                                    if (map == null) {
                                        cell.setCellValue(vformat);
                                    } else {
                                        cell.setCellValue((String)map.get(vformat));
                                    }
                                }
                            } else {
                                cell.setCellValue(String.valueOf(o));
                            }
                        } else {
                            cell.setCellValue((Double)field.get(t));
                        }

                        ++columnIndex;
                    }
                }
            }

            return wb;
        } else {
            return wb;
        }
    }

    public ExcelResult<E> readFromFile(ExcelDataFormatter edf, File file) throws Exception {
        return this.readFromFile(edf, (InputStream)(new FileInputStream(file)));
    }

    public ExcelResult<E> readFromFile(ExcelDataFormatter edf, InputStream inputStream) throws Exception {
        Map<String, Field> textToKey = new HashMap();
        ExcelColumn _excel = null;
        Field[] var5 = this.fields;
        int var6 = var5.length;

        for(int var7 = 0; var7 < var6; ++var7) {
            Field field = var5[var7];
            _excel = (ExcelColumn)field.getAnnotation(ExcelColumn.class);
            if (_excel != null && !_excel.skip()) {
                textToKey.put(_excel.name(), field);
            }
        }

        Workbook wb = new XSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        Row title = sheet.getRow(0);
        String[] titles = new String[title.getPhysicalNumberOfCells()];

        for(int i = 0; i < title.getPhysicalNumberOfCells(); ++i) {
            titles[i] = title.getCell(i).getStringCellValue();
        }

        new ArrayList();
        E e = null;
        int rowIndex = 0;
        int columnCount = titles.length;
        Cell cell = null;
        Row row = null;
        Iterator it = sheet.rowIterator();

        while(it.hasNext()) {
            row = (Row)it.next();
            if (rowIndex++ != 0) {
                if (row == null && row.getCell(1) == null) {
                    break;
                }

                e = this.newRowData();
                boolean validated = true;
                if (this.CheckRowNull(row) > 0) {
                    for(int i = 0; i < columnCount; ++i) {
                        cell = row.getCell(i);
                        ExcelColumn column = (ExcelColumn)this.fieldAnnotationMapper.get(((Field)textToKey.get(titles[i])).getName());

                        try {
                            if ((cell == null || cell.equals("")) && column.required() && this.CheckRowNull(row) > 0) {
                                this.resultWrapper.error(rowIndex, column.name(), "", "需要必填");
                                throw new ValidationException("第" + rowIndex + "行 " + column.name() + " 出错: 需要必填");
                            }

                            this.validateCellValue(column, cell);
                            this.readCellContent(column, (Field)textToKey.get(titles[i]), cell, e, edf);
                        } catch (ValidationException var20) {
                            validated = false;
                        }
                    }
                }

                if (validated) {
                    this.resultWrapper.addResult(e);
                }
            }
        }

        return this.resultWrapper;
    }

    public void readCellContent(ExcelColumn excelColumn, Field field, Cell cell, E e, ExcelDataFormatter edf) throws Exception {
        if (cell != null) {
            String cellValue = this.getCellStringValue(cell);
            field.setAccessible(true);
            Map<String, String> map = null;
            if (edf.get(field.getName()) != null) {
                map = edf.get(field.getName());
                cellValue = (String)map.get(cellValue);
            }

            try {
                if (field.getType().equals(Date.class)) {
                    field.set(e, DateUtils.parseDate(cellValue));
                } else if (field.getType().equals(String.class)) {
                    field.set(e, cellValue);
                } else if (field.getType().equals(Long.class)) {
                    field.set(e, Long.valueOf(cellValue));
                } else if (field.getType().equals(Integer.class)) {
                    if (StringUtils.isNoneBlank(new CharSequence[]{cellValue})) {
                        if (cellValue.indexOf(".") > 0) {
                            cellValue = cellValue.replaceAll("0+?$", "");
                            cellValue = cellValue.replaceAll("[.]$", "");
                        }
                        field.set(e, Integer.valueOf(cellValue));
                    }
                } else if (field.getType().equals(BigDecimal.class)) {
                    field.set(e, BigDecimal.valueOf(Double.parseDouble(cellValue)));
                } else if (field.getType().equals(Float.class)) {
                    field.set(e, Float.parseFloat(cellValue));
                } else if (field.getType().equals(Double.class)) {
                    if (!"".equals(cellValue)) {
                        field.set(e, Double.parseDouble(cellValue));
                    }
                } else if (field.getType().equals(Boolean.class)) {
                    field.set(e, Boolean.parseBoolean(cellValue));
                }

            } catch (RuntimeException var9) {
                logger.error("excel 列格式转换出错：", var9);
                this.resultWrapper.error(cell.getRowIndex() + 1, excelColumn.name(), cellValue, "格式转化出错!");
                throw new ValidationException("第" + cell.getRowIndex() + 1 + "行 " + field.getName() + " 出错: 格式转化出错");
            }
        }
    }

    private void validateCellValue(ExcelColumn column, Cell cell) {
        String cellString = this.getCellStringValue(cell);
        Boolean b = StringUtils.isBlank(cellString);
        if (column.required() && StringUtils.isBlank(cellString)) {
            this.resultWrapper.error(cell.getRowIndex() + 1, column.name(), cellString, "需要必填");
            throw new ValidationException("第" + cell.getRowIndex() + 1 + "行 " + column.name() + " 出错: 需要必填");
        } else if (column.type() != ExcelColumnType.NONE && !this.regexMatch(column.type().getValue(), cellString)) {
            this.resultWrapper.error(cell.getRowIndex() + 1, column.name(), cellString, column.type().getDesc() + "验证失败!");
            throw new ValidationException("第" + cell.getRowIndex() + 1 + "行 " + column.name() + " 出错: " + column.type().getDesc() + "验证失败!");
        } else if (column.type() == ExcelColumnType.OTHERS && StringUtils.isNotBlank(column.regex()) && !this.regexMatch(column.regex(), cellString)) {
            this.resultWrapper.error(cell.getRowIndex() + 1, column.name(), cellString, "正则表达式 " + column.regex() + " 验证失败!");
            throw new ValidationException("第" + cell.getRowIndex() + 1 + "行 " + column.name() + " 出错: 正则表达式 " + column.regex() + " 验证失败!");
        }
    }

    private String getCellStringValue(Cell cell) {
        if (cell == null) {
            return null;
        } else {
            String cellValue = "";
            switch(cell.getCellType()) {
                case BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case NUMERIC:
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                case STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case BLANK:
                    cellValue = "";
                    break;
                case FORMULA:
                    cellValue = cell.getCellFormula();
                    break;
                default:
                    cellValue = "";
            }

            return cellValue;
        }
    }

    private boolean regexMatch(String regex, String value) {
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(value);
        return matcher.matches();
    }

    /*public static void main(String[] args) throws Exception {
        System.out.println("写Excel");
        List<ExcelDemo> list = new ArrayList();
        ExcelDemo u = new ExcelDemo();
        u.setAge("");
        u.setName("fdsafdsa1");
        u.setXx(123.23D);
        u.setYy(new Date());
        u.setLocked(false);
        u.setDb(new BigDecimal(123));
        list.add(u);
        u = new ExcelDemo();
        u.setAge("w");
        u.setName("fdsafdsa2");
        u.setXx(123.23D);
        u.setYy(new Date());
        u.setLocked(true);
        u.setDb(new BigDecimal(234));
        list.add(u);
        u = new ExcelDemo();
        u.setAge("123");
        u.setName("fdsafdsa3");
        u.setXx(123.23D);
        u.setYy(new Date());
        u.setLocked(false);
        u.setDb(new BigDecimal(2344));
        list.add(u);
        u = new ExcelDemo();
        u.setAge("22");
        u.setName("fdsafdsa4");
        u.setXx(123.23D);
        u.setYy(new Date());
        u.setLocked(true);
        u.setDb(new BigDecimal(908));
        list.add(u);
        ExcelDataFormatter edf = new ExcelDataFormatter();
        Map<String, String> map = new HashMap();
        map.put("真", "true");
        map.put("假", "false");
        edf.set("locked", map);
        ExcelResult<ExcelDemo> xx = (new ExcelUtils(ExcelDemo.class)).readFromFile(edf, new File("/Users/aaron/Downloads/x.xlsx"));
        System.out.println((new GsonBuilder()).create().toJson(xx));
    }*/

    private int CheckRowNull(Row hssfRow) {
        int num = 0;
        Iterator cellItr = hssfRow.iterator();

        while(cellItr.hasNext()) {
            Cell c = (Cell)cellItr.next();
            if (StringUtils.isNoneBlank(new CharSequence[]{c.toString()})) {
                ++num;
            }
        }

        return num;
    }
}
